Homework 6 - Creating a resume database

Author

Alexa Lauer

Published

November 4, 2024

The document includes a Python-based script that interacts with a MySQL database to run queries, execute DDL files, and display database contents in HTML format. Using SQLAlchemy for database connections, pandas for data manipulation, and .env file configuration for secure credential handling, it provides functions to connect to a database, execute SQL queries, handle various SQL and connection errors, and return results as HTML tables. Additionally, the project structure includes .env file configurations, automated DDL execution, and SQL query examples for common data retrieval tasks.

GITHUB URL: https://github.com/cmsc-vcu/cmsc408-fa2024-hw6-alexalauer

Overview and description

The project involves creating a database to capture and organize the collective skills, certifications, and roles of a small team of designers and developers in a company run by an eccentric “sensei.” This “company resume” database will store information about each team member’s skills and roles, allowing the sensei to quickly find employees with specific expertise, view individual skill profiles, and identify skill gaps.

The database will have tables for skills, employees, roles, and the relationships between them. The Skills table will store each skill’s name, description, a tag, URL, and time commitment, including skills the team aims to acquire in the future. The Employees table will capture each employee’s personal and professional information, including LinkedIn and Discord details, headshots, and bios. The Roles table defines each role’s name and display priority, supporting well-organized reports.

Two relationship tables will connect employees with skills and roles. The Employee Skills table will log when employees acquire each skill, including anticipated completion dates for in-progress certifications. The Employee Roles table will track role assignments, storing the start date for each role an employee holds. This setup will give the sensei easy access to the company’s skillset, supporting better planning and growth strategies.

Crows-foot diagram

The Crow’s Foot diagram illustrates the relationships and attributes among five key entities in a database schema designed to manage information about people, their skills, roles, and the associations between them.

  1. Entities:

    • PEOPLE: This entity contains details about individuals, including their first name, last name, email address, LinkedIn profile, headshot, Discord handle, brief biography, and the date they joined.

    • SKILLS: This entity lists various skills available, with attributes such as the skill’s name, description, associated tag, URL for more information, and the time commitment required to acquire or develop the skill.

    • PEOPLESKILLS: This associative entity captures the relationship between people and their skills, indicating which skills a person has acquired and the date of acquisition.

    • ROLES: This entity defines different roles within the organization, with attributes that include the role name and its sort priority, determining the order of importance or hierarchy.

    • PEOPLE_ROLES: This associative entity links people to the roles they hold within the organization, including the date each role was acquired.

  2. Relationships:

    • Acquisition of Skills: Each person can acquire multiple skills, creating a one-to-many relationship from PEOPLE to PEOPLESKILLS. This indicates that one person can have many skills, while each skill entry corresponds to one specific individual.

    • Role Assignments: Each person can hold multiple roles, represented by the relationship between PEOPLE and PEOPLE_ROLES. This reflects the idea that a person can assume different positions within an organization over time.

    • Skill Listings: The SKILLS entity is linked to PEOPLESKILLS, showing that a skill can be held by multiple people.

    • Role Definitions: The ROLES entity is linked to PEOPLE_ROLES, indicating that a role can be assigned to multiple individuals, showcasing the flexibility of role distribution within the organization.

Overall, this diagram provides a clear visual representation of how individuals are related to their skills and roles, facilitating better understanding and management of personnel and their capabilities within an organization.

erDiagram
    SKILLS ||--o{ PEOPLESKILLS : includes
    PEOPLE ||--o{ PEOPLESKILLS : acquires
    PEOPLE ||--o{ PEOPLE_ROLES : has
    ROLES ||--o{ PEOPLE_ROLES : assigned
    SKILLS {
        int id
        string name
        string description
        string tag
        string url
        string time_commitment
    }
    PEOPLESKILLS {
        int id
        int skills_id
        int people_id
        date date_acquired
    }
    PEOPLE {
        int id
        string first_name
        string last_name
        string email
        string linkedin_url
        string headshot_url
        string discord_handle
        string brief_bio
        date date_joined
    }
    PEOPLE_ROLES {
        int id
        int people_id
        int role_id
        date date_role_acquired
    }
    ROLES {
        int id
        string name
        int sort_priority
    }

Loading the database

No errors detected while loading: ./my-ddl.sql

Examples of data in the database

The following sections provide an overview of the schema including table names, and number of rows and columns in each table.

For the people, skills, and roles tables, a description of each table is presented along with it’s contents.

Tables and metrics in the database

TableName RecordCount ColumnCount
people 10 9
peopleroles 15 4
peopleskills 26 4
roles 0 3
skills 2 6

People table

The people table contains elements that describe the information about an employee

Below is a list of data in the people table.

sql = f"""
select * from people;
"""
run_sql_and_return_df( sql,cnx )
id first_name last_name email linkedin_url headshot_url discord_handle brief_bio date_joined
201 Alex Person 1 alex.person1@example.com https://linkedin.com/in/alexperson1 None alex#1234 A motivated designer. 2023-01-15
202 Jordan Person 2 jordan.person2@example.com https://linkedin.com/in/jordanperson2 None jordan#2345 Skilled in front-end development. 2022-11-20
203 Taylor Person 3 taylor.person3@example.com https://linkedin.com/in/taylorperson3 None taylor#3456 Passionate about UX/UI. 2021-08-05
204 Casey Person 4 casey.person4@example.com https://linkedin.com/in/caseyperson4 None casey#4567 Backend developer extraordinaire. 2023-03-12
205 Morgan Person 5 morgan.person5@example.com https://linkedin.com/in/morganperson5 None morgan#5678 Data scientist in training. 2022-07-22
206 Reese Person 6 reese.person6@example.com https://linkedin.com/in/reeseperson6 None reese#6789 Loves to work with APIs. 2021-06-11
207 Blake Person 7 blake.person7@example.com https://linkedin.com/in/blakeperson7 None blake#7890 Machine learning enthusiast. 2023-02-08
208 Alexis Person 8 alexis.person8@example.com https://linkedin.com/in/alexperson8 None alexis#8901 Front-end designer. 2020-12-14
209 Drew Person 9 drew.person9@example.com https://linkedin.com/in/drewperson9 None drew#9012 QA engineer with a keen eye. 2022-05-03
210 Robin Person 10 robin.person10@example.com https://linkedin.com/in/robinperson10 None robin#0123 Specializes in cloud services. 2023-04-19

Skills table

The skills table contains elements that describe the possible skills the employees have

Below is a list of data in the skills table.

sql = """
select * from skills
"""
run_sql_and_return_df( sql, cnx )
id name description tag url time_commitment
101 Animal Tracking The ability to track animal locations via footprints Skill 1 None Medium
102 Sock Matching Expert ability to find matching pairs of socks in a laundry pile Skill 2 None Low
103 Pencil Spinning Advanced skill in twirling a pencil around fingers without dropping it Skill 3 None Low
104 Impressive Yawning Mastery in performing dramatic yawns that influence others to yawn Skill 4 None Minimal
105 One-Handed Clapping Ability to create sound by clapping with just one hand Skill 5 None Medium
106 Spontaneous Dancing Skill in breaking into dance at random moments Skill 6 None Low
107 Refrigerator Tetris Talent in arranging food items in the fridge to maximize space Skill 7 None Medium
108 Pet Whispering The ability to hold engaging conversations with pets and understand their “replies” Skill 8 None High

Roles table

The roles table contains elements that describe all of the roles in the company an employee can hold

Below is a list of data in the roles table.

sql = """
select * from roles
"""
run_sql_and_return_df( sql, cnx )
id name sort_priority
401 Designer 10
402 Developer 20
403 Recruit 30
404 Team Lead 40
405 Boss 50
406 Mentor 60

Sample queries

List skills, names, tags and descriptions ordered by name

sql = """
SELECT skills.name, skills.tag, skills.description
FROM skills
ORDER BY skills.name ASC
"""
run_sql_and_return_df( sql, cnx )
name tag description
Animal Tracking Skill 1 The ability to track animal locations via footprints
Impressive Yawning Skill 4 Mastery in performing dramatic yawns that influence others to yawn
One-Handed Clapping Skill 5 Ability to create sound by clapping with just one hand
Pencil Spinning Skill 3 Advanced skill in twirling a pencil around fingers without dropping it
Pet Whispering Skill 8 The ability to hold engaging conversations with pets and understand their “replies”
Refrigerator Tetris Skill 7 Talent in arranging food items in the fridge to maximize space
Sock Matching Skill 2 Expert ability to find matching pairs of socks in a laundry pile
Spontaneous Dancing Skill 6 Skill in breaking into dance at random moments

List people names and email addresses ordered by last_name

sql = """
SELECT CONCAT(people.first_name, ' ', people.last_name) AS full_name, people.email
FROM people
ORDER BY people.last_name ASC;
"""
run_sql_and_return_df( sql, cnx )
full_name email
Alex Person 1 alex.person1@example.com
Robin Person 10 robin.person10@example.com
Jordan Person 2 jordan.person2@example.com
Taylor Person 3 taylor.person3@example.com
Casey Person 4 casey.person4@example.com
Morgan Person 5 morgan.person5@example.com
Reese Person 6 reese.person6@example.com
Blake Person 7 blake.person7@example.com
Alexis Person 8 alexis.person8@example.com
Drew Person 9 drew.person9@example.com

List skill names of Person 1

sql = """
SELECT 
    CONCAT(people.first_name, ' ', people.last_name) AS "Employee name",
    skills.name AS skill_name
FROM 
    peopleskills
        JOIN skills ON peopleskills.skills_id = skills.id
        JOIN people ON peopleskills.people_id = people.id
WHERE 
    people.last_name = 'Person 1'
"""
run_sql_and_return_df( sql, cnx )
Employee name skill_name
Alex Person 1 Animal Tracking
Alex Person 1 Pencil Spinning
Alex Person 1 Spontaneous Dancing

List people names with Skill 6

sql = """
SELECT 
    CONCAT(people.first_name, ' ', people.last_name) AS "Employee name"
FROM 
    peopleskills
        JOIN skills ON peopleskills.skills_id = skills.id
        JOIN people ON peopleskills.people_id = people.id
WHERE
    skills.tag = 'Skill 6'
"""
run_sql_and_return_df( sql, cnx )
Employee name
Alex Person 1
Morgan Person 5
Blake Person 7
Alexis Person 8
Drew Person 9

List people with a DEVELOPER role

sql = """
SELECT 
    CONCAT(people.first_name, ' ', people.last_name) AS "Employee name"
FROM 
    peopleroles
        JOIN roles ON peopleroles.roles_id = roles.id
        JOIN people ON peopleroles.people_id = people.id
WHERE 
    roles.name = 'Developer'
"""
run_sql_and_return_df( sql, cnx )
Employee name
Alex Person 1
Taylor Person 3
Reese Person 6
Drew Person 9
Robin Person 10

List names and email addresses of people without skills

sql = """
SELECT 
    CONCAT(people.first_name, ' ', people.last_name) AS "Employee name", people.email
FROM 
    people
        LEFT JOIN peopleskills ON people.id = peopleskills.people_id
WHERE
    peopleskills.people_id IS NULL
"""
run_sql_and_return_df( sql, cnx )
Employee name email
Casey Person 4 casey.person4@example.com

List names and tags of unused skills

sql = """
SELECT 
    skills.name, skills.tag
FROM 
    skills 
        LEFT JOIN peopleskills ON skills.id = peopleskills.skills_id
WHERE 
    peopleskills.skills_id IS NULL
"""
run_sql_and_return_df( sql, cnx )
name tag
Refrigerator Tetris Skill 7
Pet Whispering Skill 8

List people names and skill names with the BOSS role

sql = """
SELECT 
    CONCAT(people.first_name, ' ', people.last_name) AS "Employee Name",
    skills.name AS "Skill Name"
FROM 
    people
        JOIN peopleroles ON people.id = peopleroles.people_id
        JOIN roles ON peopleroles.roles_id = roles.id
        JOIN peopleskills ON people.id = peopleskills.people_id
        JOIN skills ON peopleskills.skills_id = skills.id
WHERE 
    roles.name = 'Boss';
"""
run_sql_and_return_df( sql, cnx )
Employee Name Skill Name
Jordan Person 2 Pencil Spinning
Jordan Person 2 Impressive Yawning
Jordan Person 2 One-Handed Clapping

List ids and names of unused roles

sql = """
SELECT 
    roles.id, roles.name 
FROM 
    roles
        LEFT JOIN peopleroles ON roles.id = peopleroles.roles_id
WHERE peopleroles.roles_id IS NULL
"""
run_sql_and_return_df( sql, cnx )
id name
no records returned

Reflection

I struggled a little with getting all the tables to work properly, there are a lot of small syntax nuances that will make the program crash if not done properly. The erorr messages were incredibly helpful to find some of these, but often I needed to search through any of SQL code to find where I was missing a commma or something of the sort. As it is the first table in my report still does not calculate everything correctly. For some reason it stil says that my roles table has a recordCount of 0, even though it does not. Overall, I think the assignment helped me understand how to make tables in a ddl from scratch very effectively.

For homework 7, I had difficultly at the beginning of the assingnment due to my naming conventions from homework 6 and therefore had to go back to my ddl and rename all of my columns (for the autograder). Once all of the tables were sorted out, I jumped right into the queries. I had already completed the first two the week before and needed to do the remaining ones. Starting off I was able to piece together the pieces until I hit the Developer query. I know other students had the same issue where the peopleroles table was being made but there was nothing inside of it even though the INSERT commands were present. It took hours to determine the issue was that I needed a COMMIT; after the code. From then on, I was once again able to piece everything togther. Some of the queries were difficult to get a hang of, but eventually the syntax was starting to make sense.